package persistence.dao;

import common.util.DateUtils;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashSet;
import java.sql.Statement;
import java.text.ParseException;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashSet;
import java.util.Set;
import java.util.logging.Level;
import java.util.logging.Logger;

import core.ModelObject;
import persistence.DbUtils;
import persistence.TransactionManager;
import persistence.dto.ItemDTO;
import persistence.TransactionManager;
import persistence.dto.ProductDTO;
import persistence.dto.ProductGroupDTO;
import persistence.dto.StorageUnitDTO;

public class ProductsDBDAO extends ModelObject implements IProductsDAO
{

	/**
	 * 
	 */
	private static final long serialVersionUID = 635888560472565500L;
	private static ProductsDBDAO instance;
	
	public static ProductsDBDAO getInstance()
	{
		if(instance == null)
		{
			instance = new ProductsDBDAO();
		}
		return instance;
	}
	
	private ProductsDBDAO()
	{
		
	}
	
	@Override
	public Set<ProductDTO> getAllProducts()
	{
		Set<ProductDTO> allProducts = new HashSet<ProductDTO>();
                ResultSet rs = null;
                PreparedStatement stmt = null;
                
                try{
                    String query = "select * from Products";
                    stmt = TransactionManager.getConnection().prepareStatement(query);
                    //stmt.setBoolean(1, false);
                    rs = stmt.executeQuery();
                    
                    while(rs.next())
                    {
                        int id = rs.getInt(1);
                        String barcode = rs.getString(2);
                        String description = rs.getString(3);
                        
                        //parse the date string
                        Calendar creationDate = new GregorianCalendar();
                        Date d = DateUtils.parseDateTime(rs.getString(4));
                        creationDate.setTime(d);
                        
                        float amountNum = rs.getFloat(5);
                        String amountType = rs.getString(6);
                        int shelfLife = rs.getInt(7);
                        int threeMonthSupply = rs.getInt(8);
                        
                        ProductDTO pDTO = new ProductDTO(id, amountNum, 
                                amountType, barcode, creationDate, description, 
                                shelfLife, threeMonthSupply);
                        pDTO.setRemoved(rs.getBoolean(9));
                        allProducts.add(pDTO);
                    }
                }
                catch (SQLException e) 
                {
                	System.out.println(e.getMessage());
                }
                catch(ParseException e)
                {
                	System.out.println(e.getMessage());
                }
                finally{
                    DbUtils.safeClose(rs);
                    DbUtils.safeClose(stmt);
                }
                
		return allProducts;
	}

	@Override
	public int add(ProductDTO productDTO)
	{  
            Statement keyStmt = null;
            ResultSet keyRS = null;
            PreparedStatement stmt = null;
            int id = -1; //if id isn't set, -1 is error
            
            if(productDTO.getId() != 0) 
              return productDTO.getId();
            
            try
            {
                String query = "insert into Products (barcode, "
                        + "description, creationDate, amountNum, "
                        + "amountType, shelfLife, threeMonthSupply)"
                        + "values (?,?,?,?,?,?,? )";
                stmt = TransactionManager.getConnection().prepareStatement(query);

                stmt.setString(1, productDTO.getBarcode());
                stmt.setString(2, productDTO.getDescription());
                stmt.setString(3, productDTO.getCreationDate());
                stmt.setFloat(4, productDTO.getAmountNum());
                stmt.setString(5, productDTO.getAmountType());
                stmt.setInt(6, productDTO.getShelfLife());
                stmt.setInt(7, productDTO.getThreeMonthSupply());
                if(stmt.executeUpdate() == 1)
                {
                    keyStmt = TransactionManager.getConnection().createStatement();
                    keyRS = keyStmt.executeQuery("select last_insert_rowid()");
                    keyRS.next();
                    id = keyRS.getInt(1); //ID of the new book
                }
                if (stmt != null) stmt.close();
                if (keyRS != null) keyRS.close();
                if (keyStmt != null) keyStmt.close();
            }
            catch (SQLException e) {
                    System.out.println(e.getMessage());
            }
            return id;
	}

	@Override
	public boolean edit(ProductDTO productDTO)
	{
            try{
            String query = "update Products set barcode = ?, description = ?,"
                    + "creationDate = ?, amountNum = ?, amountType = ?, shelfLife = ?,"
                    + "threeMonthSupply = ? where ID = ?";
            PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
                    
                    stmt.setString(1, productDTO.getBarcode());
                    stmt.setString(2, productDTO.getDescription());
                    stmt.setString(3, productDTO.getCreationDate());
                    stmt.setFloat(4, productDTO.getAmountNum());
                    stmt.setString(5, productDTO.getAmountType());
                    stmt.setInt(6, productDTO.getShelfLife());
                    stmt.setInt(7, productDTO.getThreeMonthSupply());
                    stmt.setInt(8, productDTO.getId());
                    if(stmt.executeUpdate() == 1)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (SQLException e) {
			System.out.println(e.getMessage());
		}
                System.out.println("unable to edit product in Products table");
                
		return false;
	}

	@Override
	public boolean remove(ProductDTO productDTO)
	{
		try{
                    String query = "update Products set removed = ? where id = ?";
                    PreparedStatement stmt = 
                    	TransactionManager.getConnection().prepareStatement(query);
                    stmt.setBoolean(1, true);
                    stmt.setInt(2, productDTO.getId());
                    if(stmt.executeUpdate() == 1)
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (SQLException e) {
			System.out.println(e.getMessage());
		}
                System.out.println("unable to delete product in Products table");
                
		return false;
	}
	
	public String getProductBarcode(int prodID)
	{
		String barcode = "";
		String query = "select barcode from Products where ID = ?";
		
		try {
			PreparedStatement stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setInt(1, prodID);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) 
			{
				barcode = rs.getString(1);
			}
		}
		catch (SQLException e) {
			System.out.println("Couldn't fetch ProductBarcode: " + e.getMessage());
		}
		return barcode;
	}

}
